-- @owner: zhanghuan
-- @date: 2024-06-19
-- @testpoint: case when 的条件使用
--step1:创建测试表1并插入数据; expect:执行成功
drop table if exists t_ustore_listagg_0050_01;
create table t_ustore_listagg_0050_01(c1 int , c2 int, c3 varchar(10)) with (storage_type=ustore);
insert into t_ustore_listagg_0050_01 values(1,3,'a1');
insert into t_ustore_listagg_0050_01 values(1,2,'a2');
insert into t_ustore_listagg_0050_01 values(1,1,'a3');
insert into t_ustore_listagg_0050_01 values(1,3,'a4');
insert into t_ustore_listagg_0050_01 values(1,2,'a5');
insert into t_ustore_listagg_0050_01 values(2,1,'a6');
insert into t_ustore_listagg_0050_01 values(2,3,'a7');
insert into t_ustore_listagg_0050_01 values(2,2,'a8');
insert into t_ustore_listagg_0050_01 values(2,1,'a9');
insert into t_ustore_listagg_0050_01 values(3,1,'a10');
insert into t_ustore_listagg_0050_01 values(3,3,'a11');
insert into t_ustore_listagg_0050_01 values(3,2,'a12');
insert into t_ustore_listagg_0050_01 values(3,1,'a13');
insert into t_ustore_listagg_0050_01 values(3,5,'a14');
insert into t_ustore_listagg_0050_01 values(3,5,'a15');
insert into t_ustore_listagg_0050_01 values(3,7,'a16');
insert into t_ustore_listagg_0050_01 values(4,1,'a17');

--step2:创建测试表2; expect:执行成功
drop table if exists t_ustore_listagg_0050_02;
create table t_ustore_listagg_0050_02(
     col_1 integer,
     col_2 bigint,
     col_3 float8,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 timestamp,
     col_12 smalldatetime,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step3:测试表2中插入数据; expect:执行成功
drop sequence if exists seq_ustore_listagg_0050;
create sequence seq_ustore_listagg_0050 increment by 1 start with 10;
insert into t_ustore_listagg_0050_02 values(1,seq_ustore_listagg_0050.nextval,1+445.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(1,seq_ustore_listagg_0050.nextval-1, 1+445.255,98*0.99, false,lpad('abc','5','@'),lpad('abc','6','b'),rpad('abc','6','e'),(interval '4 5:09:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(1,seq_ustore_listagg_0050.nextval+2, 1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','5','e'),
(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
  to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(2,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),
(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
 to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(2,seq_ustore_listagg_0050.nextval-1, 1+445.255,98*0.99,  true,lpad('abc','5','@'),lpad('abc','5','b'),rpad('abc','4','e'),
(interval '4 5:09:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),
to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(3,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99, false,lpad('abc','4','@'),lpad('abc','4','b'),rpad('abc','6','e'),
(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(3,seq_ustore_listagg_0050.nextval-1, 1+445.255,98*0.99,  true,lpad('abc','4','@'),lpad('abc','6','b'),rpad('abc','2','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(3,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99, false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(4,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99,  true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(4,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','3','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(4,seq_ustore_listagg_0050.nextval-1, 1+445.255,98*0.99,  true,lpad('abc','2','@'),lpad('abc','3','b'),rpad('abc','2','e'),(interval '4 5:06:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(5,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','6','e'),(interval '4 5:06:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(6,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','2','b'),rpad('abc','2','e'),(interval '4 5:03:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(6,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99, false,lpad('abc','2','@'),lpad('abc','2','b'),rpad('abc','2','e'),(interval '4 5:04:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd')  ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0050_02 values(7,seq_ustore_listagg_0050.nextval,   1+445.255,98*0.99,  true,lpad('abc','2','@'),lpad('abc','2','b'),rpad('abc','2','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd'),to_timestamp('2019-01-03','yyyy-mm-dd') ,lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');

--step4:case when 的条件使用; expect:执行成功
select listagg(1) within group(order by case when col_1>col_2 then col_11 else col_12 end nulls first,col_12||col_11 nulls last) over(partition by 1) from t_ustore_listagg_0050_02;
select case when 0.733333333333334=listagg(1) within group(order by case when col_1>col_2 then col_11 else col_12 end nulls first,col_12||col_11 nulls last) over(partition by col_1) then '111' else '1111111111' end from t_ustore_listagg_0050_02;
select listagg(1) within group(order by case when c1>c2 then c1 else c2 end nulls first,c1||c2 nulls last) over(partition by 1) from t_ustore_listagg_0050_01;

--step5:清理环境; expect:执行成功
drop sequence if exists seq_ustore_listagg_0050;
drop table if exists t_ustore_listagg_0050_01;
drop table if exists t_ustore_listagg_0050_02;

